Method, apparatus, and computer program product for implementing enhanced query governor functions

ABSTRACT

A method, apparatus and computer program product are provided for implementing enhanced query governor functions. Query execution includes first checking for a timeout value for a query. Responsive to identifying a timeout value for the query, an execution time for the query is reset and a monitor for each timeout value for the query is started. Then the execution of the query is started. The execution of predefined events is monitored during the execution of the query. The predefined events include a begin or end of processing of at least one of a trigger and a user defined function (UDF). Execution status of the query is periodically checked. Responsive to identifying the query is executing, checking for any expired timeout value is performed. The execution of the query is halted responsive to an identified expired timeout value.

FIELD OF THE INVENTION

The present invention relates generally to the database managementfield, and more particularly, relates to a method, apparatus andcomputer program product for implementing enhanced query governorfunctions.

DESCRIPTION OF THE RELATED ART

Databases are computerized information storage and retrieval systems.Databases are managed by systems and may take the form of relationaldatabases and hierarchical databases. A Relational Database ManagementSystem (RDBMS) is a database system that uses relational techniques forstoring and retrieving data. Relational databases are organized intotables consisting of rows (tuples) and columns of data. A relationaldatabase typically includes many tables, and each table includesmultiple rows and columns. The tables are conventionally stored indirect access storage devices (DASD), such as magnetic or optical diskdrives, for semi-permanent storage.

Relational Database Management System (RDBMS) software using aStructured Query Language (SQL) interface is well known in the art. TheSQL interface has evolved into a standard language for RDBMS softwareand has been adopted as such by both the American Nationals StandardOrganization (ANSI) and the International Standards Organization (ISO).

A database management system (DBMS) typically includes some form ofquery governor. Known query governors typically enable a databaseadministrator and user of the database to have queries time out if thequeries take too long. In this case queries are prevented from taking uptoo much system resources.

Current technology simply allows the database user to time out a querybased upon execution time but does take into account multiple aspects orthe breakdown of a query into executing components.

A need exists for a mechanism to enable the database user to be allowedto modify multiple query attributes including multiple executingcomponents of a query. It is desirable that a query can be broken downinto multiple query execution components, for example, data retrieval,trigger processing, and user defined function (UDF) processing, and witheach of these query execution components having an individual time outvalue.

SUMMARY OF THE INVENTION

A principal object of the present invention is to provide a method,apparatus and computer program product for implementing enhanced querygovernor functions. Other important objects of the present invention areto provide such method, apparatus and computer program product forimplementing enhanced query governor functions substantially withoutnegative effect and that overcome many of the disadvantages of prior artarrangements.

In brief, a method, apparatus and computer program product are providedfor implementing enhanced query governor functions. Query executionincludes first checking for a timeout value for a query. Responsive toidentifying a timeout value for the query, an execution time for thequery is reset and a monitor for each timeout value for the query isstarted. Then the execution of the query is started. The execution ofpredefined events is monitored during the execution of the query. Thepredefined events include a begin or end of processing of at least oneof a trigger and a user defined function (UDF). Execution status of thequery is periodically checked. Responsive to identifying the query isexecuting, checking for any expired timeout value is performed. Theexecution of the query is halted responsive to an identified expiredtimeout value.

In accordance with features of the invention, empirical data for triggerprocessing and UDF processing is used to determine whether in mostlikelihood that the query can finish within set timeout values for thetrigger and user defined function (UDF), and execution of the query isstarted only responsive to determining in most likelihood the query canfinish within the timeout values.

BRIEF DESCRIPTION OF THE DRAWINGS

The present invention together with the above and other objects andadvantages may best be understood from the following detaileddescription of the preferred embodiments of the invention illustrated inthe drawings, wherein:

FIG. 1 is a block diagram illustrating a computer system forimplementing methods for processing enhanced query governor functions inaccordance with the preferred embodiment;

FIGS. 2, 3, 4, and 5 are flow charts illustrating exemplary stepsperformed by the computer system of FIG. 1 for implementing enhancedquery governor functions in accordance with the preferred embodiment;

FIG. 6 is a diagram illustrating an exemplary data table for storing UDFand trigger data in accordance with the preferred embodiment; and

FIG. 7 is a block diagram illustrating a computer program product inaccordance with the preferred embodiment.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Referring now to the drawings, in FIG. 1 there is shown a computersystem generally designated by the reference character 100 forimplementing methods for processing enhanced query governor functions inaccordance with the preferred embodiment. Computer system 100 includes aprocessor 102 coupled to a memory 104. Computer system 100 includes amass storage 106, such as a direct access storage device (DASD), adisplay 108, and a user input 110 coupled to the processor 102.

Computer system includes an operating system 112, a database managementsystem 114 including a query optimizer 116 and a query object 118. Thequery object 118 includes a number of methods or plans capable ofperforming specific operations relevant to management of the queryobject and the execution of a query represented by such an object.Database management system 114 includes a query governor program 120 ofthe preferred embodiment including a SQL processor program 122 and auser defined function (UDF) and trigger monitor program 124, and a UDFand trigger data table 126. Computer system includes a database 130stored in the mass storage 106, and a local area network (LAN) or widearea network (WAN) 132 that couples I/O devices 134, such as personalcomputers to the computer system 100. Computer system 100 is shown insimplified form sufficient for an understanding of the presentinvention.

In accordance with features of the preferred embodiment, query governorprogram 120 of the preferred embodiment includes the SQL processorprogram 122 and UDF and trigger monitor program 124. The query governorprograms 120, 122, 124 enable the database administrator and users ofthe database to modify query attributes and to monitor and maintainempirical data for implementing enhanced query governor functions of thepreferred embodiment. With the query governor programs 120,122, 124, thecomponents of a query are broken down into query execution, i.e., dataretrieval, trigger processing, and user defined function (UDF)processing. The database user is allowed to break down a query intomultiple executing components or individual pieces, each with individualtime out values. Empirical data for multiple query attributes, such ashow long UDFs and triggers take to execute, is maintained in accordancewith features of the preferred embodiment.

Advantages are provided by the enhanced query governor functions of thepreferred embodiment. For example, the database administrator or user isenabled to modify query attributes, such as turning off certain triggerprocessing and changing a web service UDF.

It should be understood that the present invention is not limited foruse with the illustrated computer system 100. The illustrated processorsystem 100 is not intended to imply architectural or functionallimitations. The present invention can be used with various hardwareimplementations and systems and various other internal hardware devices,for example, multiple main processors.

Various commercially available processors could be used for computersystem 100, for example, an AS/400 or iSeries computer systemmanufactured and sold by International Business Machines Corporation.

Referring to FIGS. 2, 3, 4, and 5 there are shown exemplary stepsperformed by the computer system 100 for implementing methods forprocessing enhanced query governor functions in accordance with thepreferred embodiment. The query governor program 120 including SQLprocessor program 122 and UDF and trigger monitor program 124 of thepreferred embodiment perform the exemplary steps for processing enhancedquery governor functions in accordance with the preferred embodiment.

Referring first to FIG. 2, there are shown exemplary steps of the SQLprocessor program 122 starting at a block 200 for handling SQL events.First, waiting for an event is provided as indicated in a block 202.When an event is received, checking to determine if the received eventis to modify query attributes as indicated in a decision block 204. Ifso, then a modify attributes routine is performed as indicated in ablock 206. The modify attributes routine is illustrated and describedwith respect to FIG. 4. Otherwise, if the received event is not tomodify query attributes, checking whether the event is an event toexecute a query is performed as indicated in a decision block 208. Ifso, then an execute query routine is performed as indicated in a block210. The execute query routine is illustrated and described with respectto FIG. 3. Otherwise, if not an event to execute a query, then any otherknown SQL event known in the art is processed as indicated in a block212. Then the sequential operations return to block 202 to wait foranother event.

Referring now to FIG. 3, there are shown exemplary steps of the executequery routine starting at a block 300 for handling execution of queries.Before starting to process the query, checking to determine if anytimeouts exist for this query is performed as indicated in a decisionblock 301. Any timeouts for this query are set through changing thequery attributes using the modify attributes routine of FIG. 4. If notimeouts exist, then the query is executed as known in the art asindicated in a block 302. If a timeout exists, then checking todetermine based on empirical data whether in most likelihood that thequery can finish within the timeout values is performed as indicated ina decision block 303. If in most likelihood the query will not finishwithin the timeout values, then the query is not even started and thesequential steps end or exit as indicated in a block 304. Otherwise ifdetermined that the query can finish within the timeout values, then theexecution time is reset for each timeout as indicated in a block 306 andmonitors are set as indicated in a block 308. A monitor in this case isone that will track processing time of a user defined function (UDF)and/or a trigger. The monitor or monitors track the timeout valuespreviously set.

Then the execution of the query is started as indicated in a block 310.After starting the execution of the query in a separate thread, thismain thread then waits a predetermined period of time as indicated in ablock 312. This predetermined period of time or time value is asub-factor of the timeout value. This means if a limit of 10 seconds isset for UDF processing, then the predetermined period of time for thewait limit will be a division of 10 seconds, such that a wake up is setfor checking if the query is still executing as indicated in a decisionblock 314. If the query is not executing, then the sequential steps endor exit as indicated in a block 316 because the query is done executing.Otherwise, checking whether the time limit has expired is performed asindicated in a decision block 318. If any of the timeouts has beenreached, then execution of the query is halted as indicated in a block320 and a return code is set as indicated in a block 322. Then thesequential steps end or exit as indicated in a block 324. Otherwise ifno time limit has expired at decision block 318, then the sequentialsteps return to block 312 to wait for the predetermined period of time.

Referring now to FIG. 4, there are shown exemplary steps of the modifyattributes routine starting at a block 400 to set query attributes.First checking to determine if a monitor is being requested is performedas indicated in a decision block 402. If so, then the monitor is set fora timeout as indicated in a block 404. Then as indicated in a block 406,the rest of the query attributes are changed as known in the art. Thenthe sequential steps end or exit as indicated in a block 408. Otherwiseif a monitor is not being requested, then the rest of the queryattributes are changed as known in the art at block 406 and thesequential steps exit at block 408.

As described with respect to block 406 in FIG. 4, enhanced querygovernor functionality is being added to the known or existing art ofchanging query attributes. Changing query attributes generally allowsfor various other query functionalities; that is used in accordance withfeatures of the preferred embodiment to set timeout values for UDFprocessing and trigger processing.

Referring now to FIG. 5, there are shown exemplary steps of the UDF andtrigger monitor program 124 starting at a block 500 for monitoring theexecution of each UDF and each trigger such that empirical data can bemaintained on how long the UDFs and triggers take to execute. First,waiting for an event is provided as indicated in a block 502. When anevent is received, checking to determine if the received event is atrigger event begin or end as indicated in a decision block 504. If so,then the trigger event and start or stop time is recorded as indicatedin a block 506. Otherwise when the received event is not a triggerevent, checking to determine if the received event is a UDF event beginor end as indicated in a decision block 508. If so, then the UDF eventand start or stop time is recorded as indicated in a block 510.Otherwise, if not a trigger or UDF event begin or end, then any otherknown SQL event known in the art is processed as indicated in a block512. Then the sequential operations return to block 502 to wait foranother event.

Referring now to FIG. 6, there is shown an exemplary data table 126 forstoring UDF and trigger data in accordance with the preferredembodiment. The UDF and trigger data table 126 stores a start time 602,a stop time 604, and an event 606, either a UDF event or a triggerevent. The UDF and trigger data table 126 contains the start and stoptimes 602, 604 for UDFs and triggers 606 that is kept up to date by theprocessing performed as shown in the flow chart of FIG. 5.

Referring now to FIG. 7, an article of manufacture or a computer programproduct 700 of the invention is illustrated. The computer programproduct 700 includes a recording medium 702, such as, a floppy disk, ahigh capacity read only memory in the form of an optically read compactdisk or CD-ROM, a tape, a transmission type media such as a digital oranalog communications link, or a similar computer program product.Recording medium 702 stores program means 704, 706, 708, 710 on themedium 702 for carrying out the methods for implementing enhanced querygovernor functions of the preferred embodiment in the system 100 of FIG.1.

A sequence of program instructions or a logical assembly of one or moreinterrelated modules defined by the recorded program means 704, 706,708, 710, direct the computer system 100 for processing enhanced querygovernor functions of the preferred embodiment.

While the present invention has been described with reference to thedetails of the embodiments of the invention shown in the drawing, thesedetails are not intended to limit the scope of the invention as claimedin the appended claims.

1-13. (canceled)
 14. A computer-readable medium encoded with a computerprogram product for implementing enhanced query governor functions in acomputer system, said computer-readable medium consisting one of afloppy disk, a hard disk, a high capacity read only memory in the formof an optically read compact disk or CD-ROM, a DVD, a tape, a read onlymemory (ROM), and a random access memory (RAM), said computer programproduct including instructions stored on a the computer-readable medium,wherein said instructions, when executed by the computer system to causethe computer system to perform the steps of: receiving a query; breakingdown the received query into query execution components, wherein thequery execution components comprises query data retrieval processing,user defined function (UDF) processing and trigger processing;monitoring events, wherein the monitored events comprising at least anevent to modify attributes and an event to execute query; responsive tothe monitored event to modify attributes, performing a modify attributesroutine; said performing the modify attributes routine includingchecking for a monitor being requested; and responsive to a monitorbeing requested, setting timeout values for the requested monitor;wherein the requested monitor checking the user defined function (UDF)processing and the trigger processing against the set timeout values;responsive to the monitored event to execute query, performing anexecute query routine; said performing the execute query routineincluding checking for a timeout value for the query data retrieval andchecking for the set timeout values performed by the modify attributesroutine; responsive to an identified timeout value for the query dataretrieval, resetting an execution time for the query; starting therequested monitor; starting the execution of the query; monitoring theexecution of predefined events during the execution of the query; saidpredefined events including a begin or end of processing of eachrequested monitor; storing start and end processing time of the userdefined function (UDF) processing and the trigger processing in a table;periodically checking execution status of the query; checking for anexpired timeout value for the query data retrieval and for the requestedmonitor; and halting the execution of the query responsive to anidentified expired timeout value.
 15. The computer-readable mediumencoded with a computer program product for implementing enhanced querygovernor functions as recited in claim 14 wherein said instructionsfurther cause the computer system to perform said modify attributesroutine including the steps of changing query attributes.
 16. (canceled)17. The computer-readable medium encoded with a computer program productfor implementing enhanced query governor functions as recited in claim14 wherein the step of monitoring the execution of said predefinedevents includes the step of recording empirical data for said triggerprocessing, and said user defined function (UDF) processing.
 18. Thecomputer-readable medium encoded with a computer program product forimplementing enhanced query governor functions as recited in claim 17includes the steps of checking to determine based upon said recordedempirical data whether in most likelihood that the query can finishwithin timeout values for the requested monitor, and only responsive todetermining in most likelihood the query can finish execution withinsaid set timeout values for the requested monitor, starting execution ofthe query.